package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ee.maoinvest.backend.database.ConnectionManager;
import ee.maoinvest.backend.model.dao.TransformerCoefficentStandardDAO;
import ee.maoinvest.backend.model.data.ElectricityMeter;
import ee.maoinvest.backend.model.data.TransformerCoefficentStandard;

public class TransformerCoefficentStandardDAOPostgresqlImpl implements TransformerCoefficentStandardDAO, Serializable
{
	/**
	 * 
	 */
	private static final long serialVersionUID = -4878763895330278524L;

	Logger logger = Logger.getLogger(TransformerCoefficentStandardDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public TransformerCoefficentStandardDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	
//-----------------START OF BASIC CRUD-----------------------------------
	@Override
	public int insertTransformerCoefficentStandard(
			TransformerCoefficentStandard transformerCoefficentStandard) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Transformer_coefficent_standards (coefficent, dateInserted, dateUpdated, isInUse, isWrittenOff, dateOfWriteOff, isDefaultTransformerCoefficentStandard) " + 
				"VALUES (?, ?, ?, ?, ?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setBigDecimal	(	1, transformerCoefficentStandard.getCoefficent());
			preparedStatement.setDate		(	2, transformerCoefficentStandard.getDateInserted());
			preparedStatement.setObject		(	3, transformerCoefficentStandard.getDateUpdated(), Types.DATE);
			preparedStatement.setBoolean	(	4, transformerCoefficentStandard.getIsInUse());
			preparedStatement.setBoolean	(	5, transformerCoefficentStandard.getIsWrittenOff());
			preparedStatement.setObject		(	6, transformerCoefficentStandard.getDateOfWriteOff(), Types.DATE);
			preparedStatement.setBoolean	(	7, transformerCoefficentStandard.getIsDefaultTransformerCoefficentStandard());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				resultSet = preparedStatement.getGeneratedKeys();
				resultSet.next(); //answer consist only 1 row, so move the cursor just once to start to process the result
				int newId = resultSet.getInt(1); // 1, because identifiers are held in the first column 
				transformerCoefficentStandard.setTransformerCoefficentStandardID(newId);
				return rowsAffected;
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public TransformerCoefficentStandard getTransformerCoefficentStandardByID(
			int transformerCoefficentStandardID) 
	{
		resultSet = null;
		TransformerCoefficentStandard transformerCoefficentStandardRetrieved = null;
		sql = "SELECT * FROM Transformer_coefficent_standards WHERE transformerCoefficentStandardID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, transformerCoefficentStandardID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				transformerCoefficentStandardRetrieved = new TransformerCoefficentStandard();
				transformerCoefficentStandardRetrieved.setTransformerCoefficentStandardID(resultSet.getInt("transformerCoefficentStandardID"));
				transformerCoefficentStandardRetrieved.setCoefficent(resultSet.getBigDecimal("coefficent"));
				transformerCoefficentStandardRetrieved.setDateInserted(resultSet.getDate("dateInserted"));
				transformerCoefficentStandardRetrieved.setDateUpdated((Date) resultSet.getObject("dateUpdated"));
				transformerCoefficentStandardRetrieved.setIsInUse(resultSet.getBoolean("isInUse"));
				transformerCoefficentStandardRetrieved.setIsWrittenOff(resultSet.getBoolean("isWrittenOff"));
				transformerCoefficentStandardRetrieved.setDateOfWriteOff((Date) resultSet.getObject("dateOfWriteOff"));
				transformerCoefficentStandardRetrieved.setIsDefaultTransformerCoefficentStandard(resultSet.getBoolean("isDefaultTransformerCoefficentStandard"));
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return transformerCoefficentStandardRetrieved;
	}

	@Override
	public int updateTransformerCoefficentStandard(
			TransformerCoefficentStandard transformerCoefficentStandard) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Transformer_coefficent_standards " + 
				"SET coefficent = ?, dateInserted = ?, dateUpdated = ?, isInUse = ?, isWrittenOff = ?, dateOfWriteOff = ?, isDefaultTransformerCoefficentStandard = ? " + 
				"WHERE transformerCoefficentStandardID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setBigDecimal	(	1, transformerCoefficentStandard.getCoefficent());
			preparedStatement.setDate		(	2, transformerCoefficentStandard.getDateInserted());
			preparedStatement.setObject		(	3, transformerCoefficentStandard.getDateUpdated(), Types.DATE);
			preparedStatement.setBoolean	(	4, transformerCoefficentStandard.getIsInUse());
			preparedStatement.setBoolean	(	5, transformerCoefficentStandard.getIsWrittenOff());
			preparedStatement.setObject		(	6, transformerCoefficentStandard.getDateOfWriteOff(), Types.DATE);
			preparedStatement.setBoolean	(	7, transformerCoefficentStandard.getIsDefaultTransformerCoefficentStandard());
			preparedStatement.setInt		(	8, transformerCoefficentStandard.getTransformerCoefficentStandardID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public int deleteTransformerCoefficentStandard(
			int transformerCoefficentStandardID) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "DELETE FROM Transformer_coefficent_standards WHERE transformerCoefficentStandardID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			preparedStatement.setInt(1, transformerCoefficentStandardID);
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Deletion successful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.info("Deletion unsuccessful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch(SQLException e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
//-----------------END OF BASIC CRUD-----------------------------------

	@Override
	public List<TransformerCoefficentStandard> getAllTransformerCoefficentStandards()
	{
		List<TransformerCoefficentStandard> listOfTransformerCoefficentStandards = new ArrayList<TransformerCoefficentStandard>();
		TransformerCoefficentStandard transformerCoefficentStandardRetrieved = null;
		sql = "SELECT * FROM Transformer_coefficent_standards ORDER BY coefficent;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				transformerCoefficentStandardRetrieved = fillTransformerCoefficentStandardObjectWithDataReceivedFromDB();
				listOfTransformerCoefficentStandards.add(transformerCoefficentStandardRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfTransformerCoefficentStandards;
	}

	@Override
	public List<TransformerCoefficentStandard> getAllTransformerCoefficentStandardsThatAreNotWrittenOff() 
	{
		List<TransformerCoefficentStandard> listOfTransformerCoefficentStandards = new ArrayList<TransformerCoefficentStandard>();
		TransformerCoefficentStandard transformerCoefficentStandardRetrieved = null;
		sql = "SELECT * FROM Transformer_coefficent_standards WHERE isWrittenOff = false ORDER BY coefficent;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				transformerCoefficentStandardRetrieved = fillTransformerCoefficentStandardObjectWithDataReceivedFromDB();
				listOfTransformerCoefficentStandards.add(transformerCoefficentStandardRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfTransformerCoefficentStandards;
	}
	
	@Override
	public int setIsTransformerCoefficentStandardInUse(
			int transformerCoefficentStandardID, boolean isInUse) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Transformer_coefficent_standards " + 
				"SET isInUse = ? " + 
				"WHERE transformerCoefficentStandardID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setBoolean(	1, isInUse);
			preparedStatement.setInt	(	2, transformerCoefficentStandardID);
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	@Override
	public int endSpecificElectricityMeterTempRelationWithTransformerCoefficent(
			ElectricityMeter electricityMeter)
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Temp_relations_between_electricity_meter_and_its_coefficents " +
				"SET dateRelationEnded = ?, " +
				"electricityMeterDaytimeReadingAtRelationEndDate = ?, electricityMeterNightTimeReadingAtRelationEndDate = ?, electricityMeterReactivePowerReadingAtRelationEndDate = ? " +
				"WHERE electricityMeterFK = ? AND dateRelationEnded IS NULL";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setDate		(1, electricityMeter.getDateUpdated());
			preparedStatement.setBigDecimal	(2, electricityMeter.getDaytimeReadingWhileRemoving());
			preparedStatement.setBigDecimal	(3, electricityMeter.getNightTimeReadingWhileRemoving());
			preparedStatement.setBigDecimal	(4, electricityMeter.getReactivePowerReadingWhileRemoving());
			preparedStatement.setInt		(5, electricityMeter.getElectricityMeterID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	
	//Helper methods
	private TransformerCoefficentStandard fillTransformerCoefficentStandardObjectWithDataReceivedFromDB()
	{
		TransformerCoefficentStandard transformerCoefficentStandardRetrieved = null;
		try
		{
			transformerCoefficentStandardRetrieved = new TransformerCoefficentStandard();
			transformerCoefficentStandardRetrieved.setTransformerCoefficentStandardID(resultSet.getInt("transformerCoefficentStandardID"));
			transformerCoefficentStandardRetrieved.setCoefficent(resultSet.getBigDecimal("coefficent"));
			transformerCoefficentStandardRetrieved.setDateInserted(resultSet.getDate("dateInserted"));
			transformerCoefficentStandardRetrieved.setDateUpdated((Date) resultSet.getObject("dateUpdated"));
			transformerCoefficentStandardRetrieved.setIsInUse(resultSet.getBoolean("isInUse"));
			transformerCoefficentStandardRetrieved.setIsWrittenOff(resultSet.getBoolean("isWrittenOff"));
			transformerCoefficentStandardRetrieved.setDateOfWriteOff((Date) resultSet.getObject("dateOfWriteOff"));
			transformerCoefficentStandardRetrieved.setIsDefaultTransformerCoefficentStandard(resultSet.getBoolean("isDefaultTransformerCoefficentStandard"));
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return transformerCoefficentStandardRetrieved;
	}
}
